package ut.data;
import ut.util.Cache;
import ut.res.MessageType;
import ut.data.Transaction;
import ut.data.Message;
import java.util.Date;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.naming.NamingException;
public class Task{
	public static final int MATCHING = 0; // 媒合中
	public static final int MATCHED = 1; // 確定接案者 ( 預付 20% )
	public static final int TRANSLATED = 2; // 接案者完成稿件
	public static final int DEAD = 3; // 案件逾期未媒合成功，被系統自動撤回 ( 全額退款 )
	public static final int ACCEPTED = 4; // 發案者滿意確認 ( 付尾款 )
	public static final int REJECTED = 5; // 發案者不滿意確認 ( 不用付尾款 )
	public static final int REVOKED = 6; // 發案者撤回逾期案件 ( 全額退款 )
	public static final int ABANDONED = 7; // 接案者放棄 ( 全額退款 )
	/*
		註1: state > 2 代表此案件已結案，無論是否成功
	*/
	public static boolean createTask(User employer, int from, int to, String domain, String description, String content, int wordNumber, int characterNumber, int price, long publishTime, long deadTime){
		if(Database.executeTransaction(new String[]{
				"update user set money=money-" + price + " where id=" + employer.id,
				"update record set task=task+1 where id=" + employer.id,
				"insert into task(employer,from_lan,to_lan,domain,description,content,word_number,character_number,price,publish_time,dead_time) values(" + employer.id + "," + from + "," + to + ",'" + domain.replace("'", "''") + "','" + description.replace("'", "''") + "','" + content.replace("'", "''") + "'," + wordNumber +"," + characterNumber + "," + price + ",from_unixtime(" + publishTime + "),from_unixtime(" + deadTime + "))",
				"insert into transaction(user,money,paypal,type,state,time) values(" + employer.id + "," + price + ",''," + Transaction.TYPE_CREATE_TASK + "," + Transaction.STATE_COMPLETED + ",now())"
		})){
			// 刪除案主紀錄快取
			Cache.removeRecord(employer.id);
			// 更新 Session 中的資料
			employer.money = employer.money - price;
			return true;
		}else{
			return false;
		}
	}
	public static Task getTask(long id){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{

			con = Database.getConnection();
			stmt = con.createStatement();

			rs = stmt.executeQuery("select * from task where id=" + id);
			if(rs.next()){
				return new Task(rs.getLong("id"), rs.getInt("employer"), rs.getInt("from_lan"), rs.getInt("to_lan"),
					rs.getString("domain"), rs.getString("description"), rs.getString("content"), rs.getInt("word_number"), rs.getInt("character_number"), rs.getInt("price"), rs.getInt("question"),
					rs.getTimestamp("publish_time"), rs.getTimestamp("dead_time"), rs.getTimestamp("match_time"), rs.getTimestamp("translate_time"), rs.getTimestamp("finish_time"),
					rs.getInt("state"), rs.getInt("translator"), rs.getString("translation"), rs.getInt("employer_score"), rs.getInt("translator_score"));
			}else{
				return null;
			}
		}catch(NamingException e){
			e.printStackTrace();
			return null;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}
		}
	}
	public static Task getSimpleTask(long id){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{

			con = Database.getConnection();
			stmt = con.createStatement();

			rs = stmt.executeQuery("select id,employer,from_lan,to_lan,domain,description,word_number,character_number,price,question,publish_time,dead_time,state from task where id=" + id);
			if(rs.next()){
				return new Task(rs.getLong("id"), rs.getInt("employer"), rs.getInt("from_lan"),	rs.getInt("to_lan"),
					rs.getString("domain"), rs.getString("description"), null, rs.getInt("word_number"), rs.getInt("character_number"), rs.getInt("price"), rs.getInt("question"),
					rs.getTimestamp("publish_time"), rs.getTimestamp("dead_time"), null, null, null,
					rs.getInt("state"), 0, null, 0, 0);
			}else{
				return null;
			}
		}catch(NamingException e){
			e.printStackTrace();
			return null;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}
		}
	}
	// 計算所有付費案件列表的最大頁數
	public static int getMaxTaskPage(int pageSize){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select count(*) from task");
			rs.next();
			return ((rs.getInt("count(*)") - 1) / pageSize) + 1;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 取得所有付費案件列表
	public static Task[] getTasks(int pageSize, int pageNumber){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{

			con = Database.getConnection();
			stmt = con.createStatement();

			Task[] tasks = new Task[pageSize];
			rs = stmt.executeQuery("select id,employer,from_lan,to_lan,domain,description,word_number,character_number,price,publish_time,dead_time,state from task order by state,publish_time desc limit " + ((pageNumber-1)*pageSize) + "," + pageSize);
			int index = 0;
			while(rs.next()){
				tasks[index] = new Task(rs.getLong("id"), rs.getInt("employer"), rs.getInt("from_lan"), rs.getInt("to_lan"),
					rs.getString("domain"), rs.getString("description"), null, rs.getInt("word_number"), rs.getInt("character_number"), rs.getInt("price"), 0,
					rs.getTimestamp("publish_time"), rs.getTimestamp("dead_time"), null, null, null,
					rs.getInt("state"), 0, null, 0, 0);
				index++;
			}
			return tasks;
		}catch(NamingException e){
			e.printStackTrace();
			return null;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}
		}
	}
	// 計算特定發案者的付費案件列表的最大頁數
	public static int getMaxTaskPage(User employer, int pageSize){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select count(*) from task where employer=" + employer.id);
			rs.next();
			return ((rs.getInt("count(*)") - 1) / pageSize) + 1;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 取得特定發案者的付費案件列表
	public static Task[] getTasks(User employer, int pageSize, int pageNumber){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{

			con = Database.getConnection();
			stmt = con.createStatement();

			Task[] tasks = new Task[pageSize];
			// 這裡不需要取得 employer 的資料，因為就是使用者自己本身
			rs = stmt.executeQuery("select id,employer,from_lan,to_lan,domain,description,word_number,character_number,price,publish_time,dead_time,state from task where employer=" + employer.id + " order by state,publish_time desc limit " + ((pageNumber-1)*pageSize) + "," + pageSize);
			int index = 0;
			while(rs.next()){
				tasks[index] = new Task(rs.getLong("id"), employer.id, rs.getInt("from_lan"), rs.getInt("to_lan"),
					rs.getString("domain"), rs.getString("description"), null, rs.getInt("word_number"), rs.getInt("character_number"), rs.getInt("price"), 0,
					rs.getTimestamp("publish_time"), rs.getTimestamp("dead_time"), null, null, null,
					rs.getInt("state"), 0, null, 0, 0);
				index++;
			}
			return tasks;
		}catch(NamingException e){
			e.printStackTrace();
			return null;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}
		}
	}
	// 計算特定譯者的付費案件列表的最大頁數
	public static int getMaxTaskPage(int translator, int pageSize){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select count(*) from task where translator=" + translator);
			rs.next();
			return ((rs.getInt("count(*)") - 1) / pageSize) + 1;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 取得特定發案者的付費案件列表
	public static Task[] getTasks(int translator, int pageSize, int pageNumber){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{

			con = Database.getConnection();
			stmt = con.createStatement();

			Task[] tasks = new Task[pageSize];
			rs = stmt.executeQuery("select id,employer,from_lan,to_lan,domain,description,word_number,character_number,price,publish_time,dead_time,state from task where translator=" + translator + " order by state,publish_time desc limit " + ((pageNumber-1)*pageSize) + "," + pageSize);
			int index = 0;
			while(rs.next()){
				tasks[index] = new Task(rs.getLong("id"), rs.getInt("employer"), rs.getInt("from_lan"), rs.getInt("to_lan"),
					rs.getString("domain"), rs.getString("description"), null, rs.getInt("word_number"), rs.getInt("character_number"), rs.getInt("price"), 0,
					rs.getTimestamp("publish_time"), rs.getTimestamp("dead_time"), null, null, null,
					rs.getInt("state"), translator, null, 0, 0);
				index++;
			}
			return tasks;
		}catch(NamingException e){
			e.printStackTrace();
			return null;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}
		}
	}
	// 計算譯者已經表達接案意願案件列表的最大頁數
	public static int getMaxAppliedTaskPage(int translator, int pageSize){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select count(*) from application where translator=" + translator);
			rs.next();
			return ((rs.getInt("count(*)") - 1) / pageSize) + 1;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 取得譯者已經表達接案意願的案件列表
	public static Task[] getAppliedTasks(int translator, int pageSize, int pageNumber){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{

			con = Database.getConnection();
			stmt = con.createStatement();

			Task[] tasks = new Task[pageSize];
			rs = stmt.executeQuery("select task.id,task.employer,task.from_lan,task.to_lan,task.domain,task.description,task.word_number,task.character_number,task.price,task.publish_time,task.dead_time,task.state from task,application where application.translator=" + translator + " and application.id=task.id order by application.apply_time desc limit " + ((pageNumber-1)*pageSize) + "," + pageSize);
			int index = 0;
			while(rs.next()){
				tasks[index] = new Task(rs.getLong("id"), rs.getInt("employer"), rs.getInt("from_lan"), rs.getInt("to_lan"),
					rs.getString("domain"), rs.getString("description"), null, rs.getInt("word_number"), rs.getInt("character_number"), rs.getInt("price"), 0,
					rs.getTimestamp("publish_time"), rs.getTimestamp("dead_time"), null, null, null,
					rs.getInt("state"), 0, null, 0, 0);
				index++;
			}
			return tasks;
		}catch(NamingException e){
			e.printStackTrace();
			return null;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}
		}
	}
	// 偵測譯者是否已經申請過特定案件
	public static boolean checkAppliedTask(long id, int translator){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{

			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select id from application where id=" + id + " and translator=" + translator);
			return rs.next();
		}catch(NamingException e){
			e.printStackTrace();
			return false;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return false;
			}
		}
	}
	// 案主主動發送案件邀請，僅訊息傳送無實際效力
	public static boolean inviteTranslator(long id, User employer, int translator){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			rs = stmt.executeQuery("select id from task where id=" + id + " and employer=" + employer.id + " and state=" + Task.MATCHING);
			if(!rs.next()){
				return false;
			}
			con.commit();
			// 若資料檢察成功，則寄送邀請訊息給譯者
			MessageType messageType = MessageType.getMessageType(MessageType.APPLY_TASK_INVITATION);
			return Message.createMessage(translator, MessageType.getFilledContent(messageType, new String[]{employer.name, id + ""}), messageType);
		}catch(NamingException e){
			e.printStackTrace();
			return false;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return false;
				}
			}
			return false;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return false;
			}
		}
	}
	// 譯者主動表達接案意願
	public static int applyTask(long id, User translator){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			int employer;
			rs = stmt.executeQuery("select employer,id from task where id=" + id + " and state=" + Task.MATCHING);
			if(rs.next()){
				employer = rs.getInt("employer");
			}else{
				return 0;
			}
			int result = stmt.executeUpdate("insert ignore into application(id,translator,apply_time) values(" + id + "," + translator.id + ",now())");
			con.commit();
			if(result == 1){ // 若成功則寄送訊息給案主
				MessageType messageType = MessageType.getMessageType(MessageType.TASK_APPLIED);
				Message.createMessage(employer, MessageType.getFilledContent(messageType, new String[]{translator.name, id + ""}), messageType);
			}
			return result;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return -1;
				}
			}
			return -1;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 譯者主動收回接案意願
	public static int unapplyTask(long id, int translator){
		Connection con = null;
		Statement stmt = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			int result = stmt.executeUpdate("delete from application where id=" + id + " and translator=" + translator);
			con.commit();
			return result;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return -1;
				}
			}
			return -1;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 偵測譯者是否已經表達接案意願
	public static boolean checkTaskApplied(long id, int translator){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{

			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select id from application where id=" + id + " and translator=" + translator);
			return rs.next();
		}catch(NamingException e){
			e.printStackTrace();
			return false;
		}catch(SQLException e){
			e.printStackTrace();
			return false;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return false;
			}
		}
	}
	// 計算有意願的譯者列表最大頁數
	public static int getMaxApplicationPage(long id, int pageSize){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			rs = stmt.executeQuery("select count(*) from application where id=" + id);
			rs.next();
			return ((rs.getInt("count(*)") - 1) / pageSize) + 1;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			return -1;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 取得有意願的譯者列表
	public static Task.Application[] getApplications(long id, int pageSize, int pageNumber){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{

			con = Database.getConnection();
			stmt = con.createStatement();

			Task.Application[] applications = new Task.Application[pageSize];
			rs = stmt.executeQuery("select * from application where id=" + id + " order by apply_time desc limit " + ((pageNumber-1)*pageSize) + "," + pageSize);
			int index = 0;
			while(rs.next()){
				applications[index] = new Task.Application(rs.getInt("translator"), rs.getTimestamp("apply_time"));
				index++;
			}
			return applications;
		}catch(NamingException e){
			e.printStackTrace();
			return null;
		}catch(SQLException e){
			e.printStackTrace();
			return null;
		}finally{
			try{
				if(con != null){
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return null;
			}
		}
	}
	// 發案者選定接案譯者
	public static int assignTask(long id, User employer, int translator){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			// 檢察申請資料是否有對應上
			rs = stmt.executeQuery("select id from application where id=" + id + " and translator=" + translator);
			if(!rs.next()){
				return 0;
			}
			// 更新案件的資料狀態
			int result = stmt.executeUpdate("update task set state=" + Task.MATCHED + ",translator=" + translator + ",match_time=now() where id=" + id + " and state=" + Task.MATCHING);
			if(result == 0){
				return 0;
			}
			// 刪除所有申請紀錄
			stmt.executeUpdate("delete from application where id=" + id);
			// 更新譯者紀錄
			stmt.executeUpdate("update record set response=response+1 where id=" + translator);
			con.commit();
			// 刪除譯者紀錄快取
			Cache.removeRecord(translator);
			// 若成功則寄送訊息給譯者
			MessageType messageType = MessageType.getMessageType(MessageType.GET_TASK);
			Message.createMessage(translator, MessageType.getFilledContent(messageType, new String[]{employer.name, id + ""}), messageType);
			return 1;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return -1;
				}
			}
			return -1;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 接案譯者上傳稿件
	public static int updateTranslation(long id, int translator, String translation){
		Connection con = null;
		Statement stmt = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			// 更新案件的稿件內容
			int result = stmt.executeUpdate("update task set translation='" + translation.replace("'", "''") + "' where id=" + id + " and translator=" + translator + " and state=" + Task.MATCHED);
			con.commit();
			return result;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return -1;
				}
			}
			return -1;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 接案譯者確認稿件完成
	public static int confirmTranslation(long id, User translator, String translation){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			// 取得案主編號
			int employer;
			rs = stmt.executeQuery("select employer from task where id=" + id);
			if(rs.next()){
				employer = rs.getInt("employer");
			}else{
				return 0;
			}
			// 更新案件的狀態
			int result = stmt.executeUpdate("update task set state=" + Task.TRANSLATED + ",translation='" + translation.replace("'", "''") + "',translate_time=now() where id=" + id + " and translator=" + translator.id + " and state=" + Task.MATCHED);
			con.commit();
			// 若成功則寄送訊息給案主
			if(result == 1){
				MessageType messageType = MessageType.getMessageType(MessageType.TASK_TRANSLATED);
				Message.createMessage(employer, MessageType.getFilledContent(messageType, new String[]{translator.name, id + ""}), messageType);
			}
			return result;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return -1;
				}
			}
			return -1;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 接案譯者放棄完成稿件
	public static int abandonTask(long id, User translator){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			// 取得案主編號與案件價格
			int employer, price;
			rs = stmt.executeQuery("select employer,price from task where id=" + id);
			if(rs.next()){
				employer = rs.getInt("employer");
				price = rs.getInt("price");
			}else{
				return 0;
			}
			// 更新案件的狀態
			int result = stmt.executeUpdate("update task set state=" + Task.ABANDONED + ",finish_time=now() where id=" + id + " and translator=" + translator.id + " and state=" + Task.MATCHED);
			// 更新資金，建立交易紀錄
			if(result == 1){
				stmt.executeUpdate("update user set money=money+" + price + " where id=" + employer);
				stmt.executeUpdate("insert into transaction(user,money,paypal,type,state,time) values(" + employer + "," + price + ",''," + Transaction.TYPE_ABANDON_TASK + "," + Transaction.STATE_COMPLETED + ",now())");
			}
			con.commit();
			if(result == 1){
				// 寄送訊息給案主
				MessageType messageType = MessageType.getMessageType(MessageType.TASK_ABANDONED);
				Message.createMessage(employer, MessageType.getFilledContent(messageType, new String[]{translator.name, id + ""}), messageType);
			}
			return result;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return -1;
				}
			}
			return -1;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 發案者宣告案件逾期，全額退款
	public static int revokeTask(long id, User employer){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			// 取得案件價格
			int price, translator;
			rs = stmt.executeQuery("select price,translator from task where id=" + id);
			if(rs.next()){
				price = rs.getInt("price");
				translator = rs.getInt("translator");
			}else{
				return 0;
			}
			// 更新案件的狀態
			int result = stmt.executeUpdate("update task set state=" + Task.REVOKED + ",finish_time=now() where id=" + id + " and employer=" + employer.id + " and state=" + Task.MATCHED + " and now() > dead_time");
			// 更新資金，建立交易紀錄
			if(result == 1){
				stmt.executeUpdate("update user set money=money+" + price + " where id=" + employer.id);
				stmt.executeUpdate("insert into transaction(user,money,paypal,type,state,time) values(" + employer.id + "," + price + ",''," + Transaction.TYPE_REVOKE_TASK + "," + Transaction.STATE_COMPLETED + ",now())");
			}
			con.commit();
			if(result == 1){
				// 寄送訊息給譯者
				MessageType messageType = MessageType.getMessageType(MessageType.TASK_REVOKED);
				Message.createMessage(translator, MessageType.getFilledContent(messageType, new String[]{employer.name, id + ""}), messageType);
			}
			return result;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return -1;
				}
			}
			return -1;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 發案者確認稿件完成，付尾款
	public static int acceptTranslation(long id, User employer){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			// 取得譯者編號和案件價格
			int translator, price;
			rs = stmt.executeQuery("select translator,price from task where id=" + id);
			if(rs.next()){
				translator = rs.getInt("translator");
				price = rs.getInt("price");
			}else{
				return 0;
			}
			// 更新案件的狀態
			int result = stmt.executeUpdate("update task set state=" + Task.ACCEPTED + ",finish_time=now() where id=" + id + " and employer=" + employer.id + " and state=" + Task.TRANSLATED);
			// 更新譯者與案主紀錄，更新資金。建立交易紀錄
			if(result == 1){
				stmt.executeUpdate("update record set success=success+1 where id=" + translator);
				stmt.executeUpdate("update record set task_success=task_success+1 where id=" + employer.id);
				stmt.executeUpdate("update user set money=money+" + price + " where id=" + translator);
				stmt.executeUpdate("insert into transaction(user,money,paypal,type,state,time) values(" + translator + "," + price + ",''," + Transaction.TYPE_ACCEPT_TASK + "," + Transaction.STATE_COMPLETED + ",now())");
			}
			con.commit();
			// 刪除譯者與案主紀錄快取
			if(result == 1){
				Cache.removeRecord(translator);
				Cache.removeRecord(employer.id);
				// 寄送訊息給譯者
				MessageType messageType = MessageType.getMessageType(MessageType.TASK_ACCEPTED);
				Message.createMessage(translator, MessageType.getFilledContent(messageType, new String[]{employer.name, id + ""}), messageType);
			}
			return result;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return -1;
				}
			}
			return -1;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 發案者退稿，不用付尾款
	public static int rejectTranslation(long id, User employer){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			// 取得譯者編號和案件價格
			int translator, price, priceForTranslator, priceForEmployer;
			rs = stmt.executeQuery("select translator,price from task where id=" + id);
			if(rs.next()){
				translator = rs.getInt("translator");
				price = rs.getInt("price");
				priceForTranslator = price / 4;
				priceForEmployer = price - priceForTranslator;
			}else{
				return 0;
			}
			// 更新案件的狀態
			int result = stmt.executeUpdate("update task set state=" + Task.REJECTED + ",finish_time=now() where id=" + id + " and employer=" + employer.id + " and state=" + Task.TRANSLATED);
			// 更新資金，譯者取得 25% 訂金。建立交易紀錄
			if(result == 1){
				stmt.executeUpdate("update user set money=money+" + priceForTranslator + " where id=" + translator);
				stmt.executeUpdate("update user set money=money+" + priceForEmployer + " where id=" + employer.id);
				stmt.executeUpdate("insert into transaction(user,money,paypal,type,state,time) values(" + translator + "," + priceForTranslator + ",''," + Transaction.TYPE_REJECTED_TASK + "," + Transaction.STATE_COMPLETED + ",now())");
				stmt.executeUpdate("insert into transaction(user,money,paypal,type,state,time) values(" + employer.id + "," + priceForEmployer + ",''," + Transaction.TYPE_REJECT_TASK + "," + Transaction.STATE_COMPLETED + ",now())");
			}
			con.commit();
			if(result == 1){
				// 寄送訊息給譯者
				MessageType messageType = MessageType.getMessageType(MessageType.TASK_REJECTED);
				Message.createMessage(translator, MessageType.getFilledContent(messageType, new String[]{employer.name, id + ""}), messageType);
			}
			return result;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return -1;
				}
			}
			return -1;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 主動發送評價邀請，僅訊息傳送無實際效力 (案件完成三天內才能進行邀請)
	public static boolean inviteEvaluation(long id, User user){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			int receiver;
			rs = stmt.executeQuery("select employer,translator from task where id=" + id + " and TO_DAYS(now()) - TO_DAYS(finish_time) < 4");
			if(rs.next()){
				if(rs.getInt("employer") == user.id){
					receiver = rs.getInt("translator");
				}else if(rs.getInt("translator") == user.id){
					receiver = rs.getInt("employer");
				}else{
					return false;
				}
			}else{
				return false;
			}
			con.commit();
			// 若資料檢察成功，則寄送邀請評價訊息
			MessageType messageType = MessageType.getMessageType(MessageType.TASK_EVALUATION_INVITATION);
			return Message.createMessage(receiver, MessageType.getFilledContent(messageType, new String[]{user.name, id + ""}), messageType);
		}catch(NamingException e){
			e.printStackTrace();
			return false;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return false;
				}
			}
			return false;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return false;
			}
		}
	}
	// 發案者評價譯者
	public static int evaluateTranslator(long id, int employer, int score){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			// 取得譯者編號和當前分數
			int translator, translatorScore;
			rs = stmt.executeQuery("select translator,translator_score from task where id=" + id);
			if(rs.next()){
				translator = rs.getInt("translator");
				translatorScore = rs.getInt("translator_score");
			}else{
				return 0;
			}
			// 更新案件的狀態
			int result = stmt.executeUpdate("update task set translator_score=" + score + " where id=" + id + " and employer=" + employer + " and (state=" + Task.ACCEPTED + " or state=" + Task.REJECTED + " or state=" + Task.REVOKED + " or state=" + Task.ABANDONED + ")");
			// 更新譯者紀錄
			if(result == 1){
				if(translatorScore == 0){ // 尚未評價過，初次評價
					stmt.executeUpdate("update record set score=score+" + score + ",score_number=score_number+1 where id=" + translator);
				}else{ // 已評價過，修改
					stmt.executeUpdate("update record set score=score-" + translatorScore + "+" + score + " where id=" + translator);
				}
			}
			con.commit();
			// 刪除譯者紀錄快取
			if(result == 1){
				Cache.removeRecord(translator);
			}
			return result;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return -1;
				}
			}
			return -1;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	// 譯者評價發案者
	public static int evaluateEmployer(long id, int translator, int score){
		Connection con = null;
		Statement stmt = null;
		ResultSet rs = null;
		try{
			con = Database.getConnection();
			stmt = con.createStatement();
			con.setAutoCommit(false);
			// 取得案主編號和當前分數
			int employer, employerScore;
			rs = stmt.executeQuery("select employer,employer_score from task where id=" + id);
			if(rs.next()){
				employer = rs.getInt("employer");
				employerScore = rs.getInt("employer_score");
			}else{
				return 0;
			}
			// 更新案件的狀態
			int result = stmt.executeUpdate("update task set employer_score=" + score + " where id=" + id + " and translator=" + translator + " and (state=" + Task.ACCEPTED + " or state=" + Task.REJECTED + " or state=" + Task.REVOKED + " or state=" + Task.ABANDONED + ")");
			// 更新案主紀錄
			if(result == 1){
				if(employerScore == 0){ // 尚未評價過，初次評價
					stmt.executeUpdate("update record set task_score=task_score+" + score + ",task_score_number=task_score_number+1 where id=" + employer);
				}else{ // 已評價過，修改
					stmt.executeUpdate("update record set task_score=task_score-" + employerScore + "+" + score + " where id=" + employer);
				}
			}
			con.commit();
			// 刪除案主紀錄快取
			if(result == 1){
				Cache.removeRecord(employer);
			}
			return result;
		}catch(NamingException e){
			e.printStackTrace();
			return -1;
		}catch(SQLException e){
			e.printStackTrace();
			if(con != null){
				try{
					con.rollback();
				}catch(SQLException ex){
					return -1;
				}
			}
			return -1;
		}finally{
			try{
				if(con != null){
					con.rollback();
					con.setAutoCommit(true);
					con.close();
				}
				if(stmt != null){
					stmt.close();
				}
				if(rs != null){
					rs.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
				return -1;
			}
		}
	}
	/* Instance Definition */
	public long id;
	public int employer;
	public int fromLan, toLan;
	public String domain, description, content;
	public int wordNumber, characterNumber, price, question;
	public Date publishTime, deadTime, matchTime, translateTime, finishTime;
	public int state;
	public int translator; // 接案者 ID
	public String translation;
	public int employerScore, translatorScore;
	public Task(long id, int employer, int fromLan, int toLan, String domain, String description, String content, int wordNumber, int characterNumber, int price, int question, Date publishTime, Date deadTime, Date matchTime, Date translateTime, Date finishTime, int state, int translator, String translation, int employerScore, int translatorScore){
		this.id = id;
		this.employer = employer;
		this.fromLan = fromLan;
		this.toLan = toLan;
		this.domain = domain;
		this.description = description;
		this.content = content;
		this.wordNumber = wordNumber;
		this.characterNumber = characterNumber;
		this.price = price;
		this.question = question;
		this.publishTime = publishTime;
		this.deadTime = deadTime;
		this.matchTime = matchTime;
		this.translateTime = translateTime;
		this.finishTime = finishTime;
		this.state = state;
		this.translator = translator;
		this.translation = translation;
		this.employerScore = employerScore;
		this.translatorScore = translatorScore;
	}
	/* Inner Application Class */
	public static class Application{
		public int translator;
		public Date applyTime;
		public Application(int translator, Date applyTime){
			this.translator = translator;
			this.applyTime = applyTime;
		}
	}
}